<?php
/**
 * Created by PhpStorm.
 * User: 程序猴
 * Date: 2018/4/14
 *  Q Q: 505413
 * Time: 16:39
 */

namespace app\admin\controller;
use app\admin\controller\Base;
use think\Db;
class Sql extends Base
{
    public function index()
    {
        return $this->fetch();
    }
    //sql处理
    public function import()
    {
        if(request()->isAjax())
        {
            $sql = input('post.sql');
            if(empty($sql)){
                return json(['code' => -2, 'url' => 'sql/index', 'msg' => 'Sql语句不能为空']);
            }
            try{
                $sql_arr = explode(' ', $sql);
                $method  = strtoupper($sql_arr[0]);
                //默认只支持select 所以做了以下操作处理
                if($method == 'SELECT'){
                    $data = Db::query($sql);
                } else if($method == 'INSERT' || $method == 'DELETE' || $method == 'UPDATE'){
                    $data = Db::execute($sql);
                }

                if(false === $data){
                    return json(['code' => -1, 'url' => 'sql/index', 'msg' => 'Sql语句执行失败，请重试']);
                }else{
                    if(is_array($data)){
                        $str = '<table class="layui-table">';
                        if(!is_array($data[0])){ //一维数组
                            $data = array($data);
                        }
                        $str .= '<thead><tr>';
                        foreach ($data[0] as $key => $value) {
                            $str .= '<th>'.$key.'</th>';
                        }
                        $str .= '</thead></tr><tbody>';
                        foreach ($data as $key => $value) {
                            $str .= '<tr>';
                            foreach ($value as $k => $val) {
                                $str .= '<td>'.$val.'</td>';
                            }
                            $str .= '</tr>';
                        }
                        $str .= '</tbody></table>';
                    } else {
                        $str = '返回结果 : '.$data;
                    }
                    return json(['code' => 1, 'url' => 'sql/index', 'msg' => 'Sql执行成功', 'result' => $str]);
                }
            }catch (\Exception $e){
                return json(['code' => -3, 'url' => 'sql/index', 'msg' => 'Sql输入不正确，请检查Sql是否存在语法错误']);
            }
        }
    }
}